package cn.mrlong.tools.service

import cn.mrlong.tools.constant.PluginConstant
import org.apache.commons.dbcp.BasicDataSource
import org.apache.commons.dbutils.QueryRunner
import org.apache.commons.dbutils.handlers.ColumnListHandler
import java.io.IOException
import java.nio.file.Files
import java.nio.file.Path
import java.nio.file.Paths
import java.nio.file.StandardCopyOption
import java.sql.Connection
import java.sql.ResultSet
import java.sql.SQLException
import java.sql.Statement
import kotlin.io.path.Path

class DatabaseBasicService {
    var source: BasicDataSource? = null

    init {
        init()
    }

    private fun init() {
        println("init DatabaseBasicService")
        try {
            try {
                source = BasicDataSource()
                source!!.setMaxActive(1)
                source!!.setDriverClassName(DATABASE_DRIVER)
                source!!.setUrl(DATABASE_URL)
            } catch (e: Exception) {
                e.printStackTrace()
            }
            // 如果不存在,创建DB文件
            createFileAndDir()
            // 如果表不存在,创建表
            initTable()
        } catch (e: Exception) {
            e.printStackTrace()
        }
    }

    @get:Throws(SQLException::class)
    val connection: Connection
        get() {
            println("---------")
            return source!!.connection
        }

    fun closeResource(connection: Connection?, statement: Statement?, resultSet: ResultSet?) {
        try {
            resultSet?.close()
            statement?.close()
            connection?.close()
        } catch (e: Exception) {
            e.printStackTrace()
        }
    }

    private fun createFileAndDir() {
        //"C:\Users\Administrator\.ideaNotebooksFile"
        if (!Files.exists(PluginConstant.PROJECT_DB_DIRECTORY_PATH)) {
            try {
                Files.createDirectories(PluginConstant.PROJECT_DB_DIRECTORY_PATH)
            } catch (e: IOException) {
                e.printStackTrace()
            }
        }
        //"C:\Users\Administrator\.ideaNotebooksFile\notebooks.db"
        if (!Files.exists(PluginConstant.DB_FILE_PATH)) {
            try {
                val path = Files.createFile(PluginConstant.DB_FILE_PATH)
                println("db path : " + path.toAbsolutePath())
            } catch (e: IOException) {
                e.printStackTrace()
            }
        }
        //创建图片文件夹.
        if (!Files.exists(PluginConstant.IMAGE_DIRECTORY_PATH)) {
            try {
                Files.createDirectories(PluginConstant.IMAGE_DIRECTORY_PATH)
            } catch (e: IOException) {
                e.printStackTrace()
            }
        }

        //创建图片临时文件夹.
        if (!Files.exists(PluginConstant.TEMP_IMAGE_DIRECTORY_PATH)) {
            try {
                val path = Files.createDirectories(PluginConstant.TEMP_IMAGE_DIRECTORY_PATH)

            } catch (e: Exception) {
                e.printStackTrace()
            }
        }
    }

    /**
     * 如果不存在就初始化表
     */
    private fun initTable() {
        val createNotebookSQL = "CREATE TABLE IF NOT EXISTS notebook (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "title TEXT," +
                "show_order INTEGER," +
                "create_time INTEGER," +
                "update_time INTEGER" +
                ")"


        val createChapterSQL = "CREATE TABLE IF NOT EXISTS chapter (" +
                "id INTEGER  PRIMARY KEY AUTOINCREMENT," +
                "title TEXT," +
                "notebook_id INTEGER," +
                "show_order INTEGER," +
                "create_time INTEGER," +
                "update_time INTEGER" +
                ")"


        val createNoteSQL = "CREATE TABLE IF NOT EXISTS note (" +
                "id INTEGER PRIMARY KEY AUTOINCREMENT," +
                "title TEXT," +
                "description TEXT," +
                "chapter_id INTEGER," +
                "notebook_id INTEGER," +
                "show_order INTEGER," +
                "content TEXT," +
                "source TEXT," +
                EDITOR_OFFSET_START + " INTEGER," +
                EDITOR_OFFSET_END + " INTEGER," +
                IMAGE_RECORDS + " TEXT," +
                "type TEXT," +
                "create_time INTEGER," +
                "update_time INTEGER" +
                ")"


        //注意, SQLite 似乎不支持一次添加多个字段; 所以分开添加
        val addColOffsetStart = "ALTER TABLE note ADD COLUMN " + EDITOR_OFFSET_START + " Integer DEFAULT 0;"
        val addColOffsetEnd = "ALTER TABLE note ADD COLUMN " + EDITOR_OFFSET_END + " Integer DEFAULT 0;"
        //注意, 再次增加一个列,记录图片信息
        val addColImageRecords = "ALTER TABLE note ADD COLUMN " + IMAGE_RECORDS + " TEXT;"
        try {
            val queryRunner = QueryRunner(source)
            queryRunner.update(createNotebookSQL)
            queryRunner.update(createChapterSQL)
            queryRunner.update(createNoteSQL)
            val offsetColExists = isColumnExists("note", EDITOR_OFFSET_START, queryRunner)
            //如果不存在该字段 , 那么升级数据库,添加该字段;
            if (!offsetColExists) {
                queryRunner.update(addColOffsetStart)
                queryRunner.update(addColOffsetEnd)
            }
            val imageRecordsColExists = isColumnExists("note", IMAGE_RECORDS, queryRunner)
            if (!imageRecordsColExists) {
                queryRunner.update(addColImageRecords)
            }
        } catch (throwables: SQLException) {
            throwables.printStackTrace()
        }
    }

    /**
     * 判断是否存在某个字段的方法
     *
     * @param table       表
     * @param column      字段/列
     * @param queryRunner 查询
     * @return true 存在该字段 false 不存在该字段
     * @throws SQLException sql异常
     */
    @Throws(SQLException::class)
    private fun isColumnExists(table: String, column: String, queryRunner: QueryRunner): Boolean {
        var isExists = false
        val name = queryRunner.query("PRAGMA table_info($table)", ColumnListHandler<String>("name"))
        for (s in name) {
            if (column.equals(s, ignoreCase = true)) {
                isExists = true
                break
            }
        }
        return isExists
    }

    companion object {
        private const val DATABASE_DRIVER = "org.sqlite.JDBC"
        private val DATABASE_URL = "jdbc:sqlite:" + PluginConstant.DB_FILE_PATH
        private const val EDITOR_OFFSET_START = "offset_start"
        private const val EDITOR_OFFSET_END = "offset_end"
        private const val IMAGE_RECORDS = "image_records"
    }
}
